﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

using System.Reflection;
using System.Runtime.InteropServices;


using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
namespace CAATS_Reader
{
    public partial class 数据库查看工具 : Form
    {
        private ApplicationClass myExcel;
        private Workbook myBook;
        //
        private string currSheet;
        private int currRow;
        private int currColCount;
        private int currRowCount;
        public 数据库查看工具()
        {
            InitializeComponent();
        }
        public void exportData(string file_name)
        {
            DBConnection.Active = DBConnection.Open("10.10.100.84", "info", "root", "daiguanzhong");
            if (DBConnection.Active == null)
                DBConnection.Active = DBConnection.Open("127.0.0.1", "info", "root", "daiguanzhong");
            //如果连接失败，提示并退出
            if (DBConnection.Active == null)
            {
                MessageBox.Show("数据库连接失败！");
                return;
            }
            else
            {
            }
            New();
            string sql = "SELECT * FROM info.info";
            MySqlDataReader reader = DBConnection.Active.Reader(sql);

            SetCurrentSheet("info");

            string[] infoheads = new string[22];
            infoheads[0] = "FullName";
            infoheads[1] = "Title";
            infoheads[2] = "School";
            infoheads[3] = "Country";
            infoheads[4] = "FullAddress";
            infoheads[5] = "Organization";
            infoheads[6] = "Tel";
            infoheads[7] = "Fax";
            infoheads[8] = "EMail";
            infoheads[9] = "PassportNUmber";
            infoheads[10] = "DateOfIssue";
            infoheads[11] = "DateOfExpiry";
            infoheads[12] = "PRC";
            infoheads[13] = "Nationality";
            infoheads[14] = "GVRCnt";
            infoheads[15] = "OVRCnt";
            infoheads[16] = "GOVRCnt";
            infoheads[17] = "DateCheckIn";
            infoheads[18] = "DateDeparture";
            infoheads[19] = "GVRSmokingCnt";
            infoheads[20] = "OVRSmokingCnt";
            infoheads[21] = "GOVRSmokingCnt";


            Row(infoheads);
            while (reader.Read())
            {
                string[] infos = new string[22];
                for (int i = 0; i < 22; i++  )
                {
                    infos[i] = reader[infoheads[i]].ToString();

                }

                Row(infos);
                //Student st = new Student();
                //st.Name = reader["Name"].ToString();
                //st.Surname = reader["Surname"].ToString();
                //st.Age = Convert.ToInt32(reader["Age"].ToString());
                //st.Address = reader["Address"].ToString();
                //students.Add(st);


            }

            using (SaveFileDialog f = new SaveFileDialog() { Filter = "*.xls|*.xls", AddExtension = true })
            {
                if (f.ShowDialog() == DialogResult.OK)
                {
                    End(f.FileName);
                    MessageBox.Show("数据导出成功！");
                }
            }

            ///--------------------------------------------------------------


        }
        private void button1_Click(object sender, EventArgs e)
        {
            exportData("d:/home/e.xls");
        }

        public void Read(string excel)
        {
            //DTS
            Missing miss = Missing.Value;

            //OPEN
            myExcel = new ApplicationClass();
            myBook = myExcel.Workbooks.Open(excel, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);

        }
        public void New()
        {
            //
            myExcel = new ApplicationClass();
            myBook = myExcel.Workbooks.Add(Type.Missing);
        }

        //追加一行数据
        public void Row(params string[] vs)
        {
            //get sheet
            Worksheet sheet = myBook.Sheets[currSheet] as Worksheet;

            //Write
            for (int i = 0; i < vs.Length; i++)
            {
                //set local
                Range range = sheet.Cells[currRow, i + 1] as Range;
                range.NumberFormatLocal = "@";

                //set value
                sheet.Cells[currRow, i + 1] = vs[i];
            }

            //next
            currRow += 1;
        }
        //获得数据行数
        public int RowNum()
        {
            return currRowCount;
        }

        //获得数据列数
        public int ColNum()
        {
            return currColCount;
        }

        //
        public void SetCurrentSheet(string sheet)
        {
            //
            currSheet = sheet;

            //reset
            currRow = 1;
            currColCount = 0;
            currRowCount = 0;

            //如果sheet不存在，就创建
            Worksheet excelSheet = null;

            try
            {
                excelSheet = myBook.Sheets[currSheet] as Worksheet;
            }
            catch
            {
                excelSheet = myBook.Sheets.Add(myBook.Sheets.get_Item(myBook.Sheets.Count), Type.Missing, 1, XlSheetType.xlWorksheet) as Worksheet;
                excelSheet.Name = currSheet;
            }

            //Set Row Count
            currRowCount = excelSheet.UsedRange.Rows.Count;
            currColCount = excelSheet.UsedRange.Columns.Count;
        }

        //End
        public void End(string excel)
        {
            //
            myExcel.DisplayAlerts = false;

            //删除Sheet1～3
            ((Worksheet)myBook.Sheets["Sheet1"]).Delete();
            ((Worksheet)myBook.Sheets["Sheet2"]).Delete();
            ((Worksheet)myBook.Sheets["Sheet3"]).Delete();

            //Save
            myBook.SaveAs(excel, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            //
            myBook.Close(false, Type.Missing, Type.Missing);
            myExcel.Quit();

            //
            myBook = null;
            myExcel = null;
        }
    }
}
